Merge 2 SELECT statements

[jarvis]

Hi All,

 

I really hope someone can help. Am a novice a this stuff and spent ages getting no where. I hope this makes some sort of sense...

 

I currently have the following MySQL statement:

SELECT pm1.meta_value AS 'ORDER No', pm1.post_id AS 'TICKET No'
FROM $wpdb->postmeta pm, $wpdb->postmeta pm1
WHERE pm.meta_key = '_tribe_wooticket_event'
AND pm.meta_value =263
AND pm1.meta_key = '_tribe_wooticket_order'
AND pm.post_id = pm1.post_id
ORDER BY `pm1`.`post_id` DESC

This returns the following:

 

ORDER No TICKET No

427             430

427              429

427              428

416              424

416              423

416              422

420              421

417              419

417              418

 

Basically, 1 order (427) can contain 3 tickets (430,429,428) - for example!

 

I then have another MySQL query:

SELECT pm.post_id AS 'Order No', pm.meta_value AS 'Guest Name'
FROM $wpdb->postmeta pm
WHERE pm.post_id = '427'
AND pm.meta_key LIKE '% Attendee Name'
LIMIT 0 , 30

This returns the following:

 

Order No  Guest Name

427         John Smith

427         Fred Bloggs

427         AN Other

 

What I need to do, is somehow combine the 2 statements so I end up with 

 

ORDER No  TICKET No   Guest Name

427              430              John Smith

427              429              Fred Bloggs

427              428              AN Other

416              424              Guest A

416              423              Guest B

416              422              Guest C

 

Not sure if it helps but whenever a Name is added, it stores like so:

Meta Key               Meta Value

1 Attendee Name  John Smith

2 Attendee Name  Fred Bloggs

3 Attendee Name  AN Other

 

Any help on this would be much appreciated!

 

Thanks in advanced!!!

 

[Muddy_Funster]

Going to need to see a describe of your tables please.

[Barand]

I think it should be something like this

SELECT 
    pm1.meta_value AS 'ORDER No'
    , pm1.post_id AS 'TICKET No'
    , pm2.meta_value AS 'Guest Name'
FROM $wpdb->postmeta pm
INNER JOIN $wpdb->postmeta pm1
    ON pm.post_id = pm1.post_id AND pm1.meta_key = '_tribe_wooticket_order'
INNER JOIN $wpdb->postmeta pm2
    ON pm.post_id = pm2.post_id AND pm2.meta_key LIKE '% Attendee Name'
WHERE pm.meta_key = '_tribe_wooticket_event'
AND pm.meta_value = 263
LIMIT 0 , 30

[jarvis]

Thanks Barand, unfortunately, that returns 0 results

 

Thank you for your time though

[Barand]

It would help if you posted some sample data so we can see what it looks like and help us navigate those meta-keys and meta-value fields

[jarvis]

Hi Barand,

 

Sure, no problem. is this any good:

SELECT * 
FROM $wpdb->postmeta pm, $wpdb->postmeta pm1, $wpdb->postmeta pm2
WHERE pm.meta_key =  '_tribe_wooticket_event'
AND pm.meta_value =263
AND pm1.meta_key =  '_tribe_wooticket_order'
AND pm.post_id = pm1.post_id
AND pm2.meta_key LIKE  '% Attendee Name'
AND pm1.meta_value = pm2.post_id
ORDER BY  `pm1`.`post_id` DESC 
LIMIT 0 , 30

Produces:

 

 

 

 

  meta_id post_id meta_key meta_value meta_id post_id meta_key meta_value meta_id post_id meta_key meta_value 2729 430 _tribe_wooticket_event 263 2728 430 _tribe_wooticket_order 427 2703 427 1 Attendee Name John Smith 2729 430 _tribe_wooticket_event 263 2728 430 _tribe_wooticket_order 427 2706 427 2 Attendee Name Martin Frank 2729 430 _tribe_wooticket_event 263 2728 430 _tribe_wooticket_order 427 2709 427 3 Attendee Name Chris B Bacon 2725 429 _tribe_wooticket_event 263 2724 429 _tribe_wooticket_order 427 2703 427 1 Attendee Name John Smith 2725 429 _tribe_wooticket_event 263 2724 429 _tribe_wooticket_order 427 2706 427 2 Attendee Name Martin Frank 2725 429 _tribe_wooticket_event 263 2724 429 _tribe_wooticket_order 427 2709 427 3 Attendee Name Chris B Bacon 2721 428 _tribe_wooticket_event 263 2720 428 _tribe_wooticket_order 427 2703 427 1 Attendee Name John Smith 2721 428 _tribe_wooticket_event 263 2720 428 _tribe_wooticket_order 427 2706 427 2 Attendee Name Martin Frank 2721 428 _tribe_wooticket_event 263 2720 428 _tribe_wooticket_order 427 2709 427 3 Attendee Name Chris B Bacon 2661 424 _tribe_wooticket_event 263 2660 424 _tribe_wooticket_order 416 2530 416 1 Attendee Name John Smith 2661 424 _tribe_wooticket_event 263 2660 424 _tribe_wooticket_order 416 2533 416 2 Attendee Name Fred Bloggs 2661 424 _tribe_wooticket_event 263 2660 424 _tribe_wooticket_order 416 2536 416 3 Attendee Name Bob Squrepants 2657 423 _tribe_wooticket_event 263 2656 423 _tribe_wooticket_order 416 2530 416 1 Attendee Name John Smith 2657 423 _tribe_wooticket_event 263 2656 423 _tribe_wooticket_order 416 2533 416 2 Attendee Name Fred Bloggs 2657 423 _tribe_wooticket_event 263 2656 423 _tribe_wooticket_order 416 2536 416 3 Attendee Name Bob Squrepants 2653 422 _tribe_wooticket_event 263 2652 422 _tribe_wooticket_order 416 2530 416 1 Attendee Name John Smith 2653 422 _tribe_wooticket_event 263 2652 422 _tribe_wooticket_order 416 2533 416 2 Attendee Name Fred Bloggs 2653 422 _tribe_wooticket_event 263 2652 422 _tribe_wooticket_order 416 2536 416 3 Attendee Name Bob Squrepants 2646 421 _tribe_wooticket_event 263 2645 421 _tribe_wooticket_order 420 2636 420 1 Attendee Name John Smith 2595 419 _tribe_wooticket_event 263 2594 419 _tribe_wooticket_order 417 2578 417 1 Attendee Name Chris B Bacon 2595 419 _tribe_wooticket_event 263 2594 419 _tribe_wooticket_order 417 2581 417 2 Attendee Name AN Other 2591 418 _tribe_wooticket_event 263 2590 418 _tribe_wooticket_order 417 2578 417 1 Attendee Name Chris B Bacon 2591 418 _tribe_wooticket_event 263 2590 418 _tribe_wooticket_order 417 2581 417 2 Attendee Name AN Other

 

Please let me know if you need anything else

 

 - http://picpaste.com/export-BXzfCZN3.gif

[jarvis]

Sorry I'm trying to paste but it loses the formatting...

[Barand]

What a mess!

Sometimes the meta_value is the OrderNo, at other times the postID is the OrderNo. Then you need to match postID, which looks like an INT field, with integer values held in a char field. Ugh!

[Barand]

I've changed the join condition for pm2

SELECT DISTINCT
    pm1.meta_value AS 'ORDER No'
    , pm1.post_id AS 'TICKET No'
    , pm2.meta_value AS 'Guest Name'
FROM $wpdb->postmeta pm
INNER JOIN $wpdb->postmeta pm1
    ON pm.post_id = pm1.post_id AND pm1.meta_key = '_tribe_wooticket_order'
INNER JOIN $wpdb->postmeta pm2
    ON pm1.meta_value = pm2.post_id AND pm2.meta_key LIKE '% Attendee Name'
WHERE pm.meta_key = '_tribe_wooticket_event'
AND pm.meta_value = 263;

[jarvis]

Thanks Barand, that's got me more or less there (apologies for the delay getting back to you too!)