SQL query join elements

Question

Hi guys. I will re-write my doubt to be more easy to understand. I have one table named SeqNumbers that have only one collumn of data named PossibleNumbers, that has value from 1 to 10.000. Then I have another Table named Terminals and one of the collumns have the serial numbers of the terminals. What I want is get all the SerialNumbers that not exists in the Terminals table from 1 to 10.000. I've created the SeqNumbers table only to do this... maybe there's another solution without using it... that's fine to me. The query I have is:

SELECT PossibleNumbers from SeqNumbers Where PossibleNumbers NOT IN (SELECT SerialNumbers from Terminals)

Basically I want to list ALL serial numbers of terminals that doesn't exists in the database. This Query works fine I think... but the problem is that I don't want all results in a single collumn.. I want these results displayed in 4 or 5 collumns. For my porpuse I can only use the results from the query like that. I cannot use programmatically methods to do that... :( :(

Hope this is more clear now... Thanks for all the help...

Answer 1

select x, x+1000  from tablename

Will that do it for you?

Answer 2

You can use a scripting language to parse the MySQL results to format it anyway you like. Are you using PHP to access the database? If so, let me know and I can cook one up for you.

I just saw your new updated question. In this case the order of the columns will be ordered by your SELECT statement and you can also sort too. Here is an example:

SELECT Column1, Column2 FROM my_table ORDER BY Column1, Column2 ASC

Answer 3

If I'm reading this right, you'd probably have to do a self join; something like:

SELECT
   LeftValues.ColA,
   RightValues.ColA AS ColB
FROM YourTable LeftValues
    LEFT JOIN YourTable RightValues ON LeftValues.ColA = RightValues.ColA - 1000
WHERE LeftValues.ColA < 1000

Note: Use the JOIN that makes sense for you (left if you are willing to accept NULLs in ColB, inner if you only want them where both values exist)