Union

Is there a way to stop field_a overriding?

[scraptoft]

"SELECT event_id, location_id FROM events WHERE url='$pageurl'
UNION
SELECT venue_id, location_id FROM venues WHERE url='$pageurl'

The above retrieves two results and both contain event_id and location_id - presumably the UNION builds the collumn venue_id into event_id?

What I am trying to achieve is for venue_id to stay as venue_id.

I hope I have made myself clear enough, please bare with me as I have only used basic selects before.

Is there any alternative I can use or changes to my code?

Cheers

[coopster]

Much of how the UNION is going to work may be database-dependent, such as columns being of the same type and size, etc. But, you are saying that the venue_id is the same as the event_id so we'll assume all will work fine. Typically, the first SELECT statement will be the one used to determine column names. If you need to differentiate between the two when processing the result set, you can always add a constant ...

SELECT 'event' AS idType, event_id, location_id FROM events WHERE url='$pageurl'
UNION
SELECT 'venue' AS idType, venue_id, location_id FROM venues WHERE url='$pageurl

[Gibble]

Or something like

SELECT event_id, null as 'venue_id', location_id FROM events WHERE url='$pageurl'
UNION
SELECT null, venue_id, location_id FROM venues WHERE url='$pageurl'

[scraptoft]

The collumns are of the same type and size (almost identical). So I think this is all I need.

Could you elaborate on the 'venue' and 'idType', please? I can't figure out what I should be puting there.

Thanks for helping (talk about quick service :-)! )

[scraptoft]

@Gibble - I replied before I had seen your post.

Your code works great thanks alot - Although, I am just wondering if it is common practice to use a method like this - it seams like a bit of a cheeky work around? Forgive me if I am wrong - it works good none the less.

Regards

[Gibble]

Ya, it's probably the most elegant approach. I was trying to think up a way to do it with joins...but this is probably the fastest, most accurate way.

[edited by: Gibble at 4:52 pm (utc) on Oct. 2, 2007]

[scraptoft]

Hope I didn't strain your brain too much :-).

I too was trying to think of a way I could utilise join however I havn't quite grasped joins so that went completely over my head.

Thanks again.

[Gibble]

The problem is, you're not really joining, you're just aggregating two similar result sets. So a union is the best approach.

[syber]

UNION is not a substitute for JOIN.

If you want to see both the event_id and the venue_id, then you must do a JOIN.

Noting that location_id is in both tables, you could do something like:

SELECT events.event_id, events.location_id, venues.venue_id
FROM events JOIN venues ON events.location_id = venues.venue_id

[Gibble]

But syber, nothing from the initial where clause indicates those two tables were to be joined on that column, despite them having the same name, and likely referencing the same table.

[syber]

True, I was just trying to show the difference between a JOIN and a UNION.

I suspect that what he really wants is a JOIN.