Need help with a basic SQL query

software development

#1

I have 2 tables both are totally unrelated, but I need to query both to extract data. For example, I have a table called news1 and another called news2. I want to query everything from news1 within a certain date and everything from news2 within a certain date and have the results outputted. I am getting problems where it concatenates the results of news2 to news1.

So basically data in news1 is something like:

Title Comment
Hi There

And data in news2 is something like

Title Comment
Not here

I would like the output to be printed as 2 different rows

“hi there”
“not here”

Not as 1 row : “hi there | not here”

Any clues? Thanks.


#2

Hi. In your example, I didn’t see “date”. (Your filter).

However, in MySQL, a Union will combine data from 2 tables into 1 resultset. Add the All to get duplicate records. (Not in your example, but if Hi There was in news1 and news2, if you have Union All it will show the 2 records. Omitting the All will only show a single Hi There.)

SELECT *
FROM news1
UNION ALL
SELECT *
FROM news2

If this doesn’t produce the results you’re expecting, tell me how what you want differs from what you get. (The query was tested in PHPMyAdmin and works based on your described problem.)

Put a where clause to filter on dates, etc.

I check in here from time to time.

Jim


#3

Hi Jim,

It works. The only difference was because news1 has a different number of columns compared to news2, so I had to query the individual columns e.g.

SELECT news1.title, news1.content FROM news1
UNION ALL
SELECT news2.title, news2.content FROM news2

I was rather vague in my example above, but the information you gave was all I needed. Thanks for your help.

-Vic


#4

Vic,

Just for fun I tried…

SELECT title1, content1
FROM news1
UNION ALL
SELECT title, content
FROM news2

and it worked fine. So, no need to qualify your table names with the news1. etc. I didn’t try, but I’m wondering if I wouldn’t have changed the field names, then put a where clause on, I’ll bet I’d have to qualify the names.

And, apparently, whatever order the columns are in first are what the field name in the result set is.

When I first started playing with SQL it was in Microsoft Access so I like to see what is the same and what is different as I use PHP/MySQL.

Also, you’re probably aware of this, but in a long query you could also do this:

SELECT title T1, content C1
FROM news1
UNION ALL
SELECT title T2, content C2
FROM news2

to shorten news1.title="blah"
to T1=“blah”

to shorten and make unique names.

Jim