SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Problem with join syntax

    Hello there.
    First of all I must say that I am a newbie when it comes to MySQL.
    Here is my problem.
    I need JOIN the `tbl_flight` with `tbl_flight_name` for this output:
    Code:
    mysql> SELECT
    	b.myFlight,
    	a.myArea, 
            a.myFlightName,
    IF(
    	char_length(a.myArea)<= 4,
    	b.myFlight,
    	concat(
    		b.myFlightArea,
    		'-',
    		a.myFlightName
    	)
    ) output
    FROM
    	tbl_flight a
    JOIN tbl_flight_name b ON a.myArea = b.myFlight;
    
    +----------+------------+--------------------------+-------------------------------+
    | myFlight | myArea     | myFlightName             | output                        |
    +----------+------------+--------------------------+-------------------------------+
    | XY5QU1   | XY5QU1     | UO PT                    | XY5Q-UO PT                    |
    | WZ7M     | WZ7M       | ZN CT                    | WZ7M                          |
    | WZ7M     | WZ7M       | ZN CT                    | WZ7M                          |
    +----------+------------+--------------------------+-------------------------------+
    5 rows in set
    But I need this other output when show the `myFlightName` in output:
    Code:
    +----------+------------+--------------------------+-------------------------------+
    | myFlight | myArea     | myFlightName             | output                        |
    +----------+------------+--------------------------+-------------------------------+
    | XY5QU1   | XY5QU1     | UO PT                    | XY5Q-UO PT                    |
    | WZ7M     | WZ7M       | ZN CT                    | ZN CT                         |
    | WZ7M     | WZ7M       | ZN CT                    | ZN CT                         |
    +----------+------------+--------------------------+-------------------------------+
    Can you help me?
    Thank you very much in advance.
    Cheers.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You mean this?
    Code:
    SELECT
    	b.myFlight,
    	a.myArea, 
            a.myFlightName,
    IF(
    	char_length(a.myArea)<= 4,
    	b.myFlightName,
    	concat(
    		b.myFlightArea,
    		'-',
    		a.myFlightName
    	)
    ) output
    FROM	tbl_flight a
    INNER JOIN tbl_flight_name b 
    ON a.myArea = b.myFlight;

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hello.
    Thank you for reply. Your suggestion working.
    Good bye.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry guido2004, I have new problem with your query.
    Code:
    mysql> SELECT
    	b.myFlight,
    	a.myArea, 
            a.myFlightName,
    IF(
    	char_length(a.myArea)<= 4,
    	b.myFlightName,
    	concat(
    		b.myFlightArea,
    		'-',
    		a.myFlightName
    	)
    ) output
    FROM	tbl_flight a
    INNER JOIN tbl_flight_name b 
    ON a.myArea = b.myFlight;
    
    +----------+------------+--------------------------+-------------------------------+
    | myFlight | myArea     | myFlightName             | output                        |
    +----------+------------+--------------------------+-------------------------------+
    | XY5QU1   | XY5QU1     | UO PT                    | XY5Q-UO PT                    |
    | WZ7M     | WZ7M       | ZN CT                    | ZN CT                         |
    | WZ7M     | WZ7M       | ZN CT                    | ZN CT                         |
    +----------+------------+--------------------------+-------------------------------+
    I need this different output, can you help me?
    Code:
    +----------+------------+--------------------------+-------------------------------+
    | myFlight | myArea     | myFlightName             | output                        |
    +----------+------------+--------------------------+-------------------------------+
    | XY5QU1   | XY5QU1     | UO PT                    | ZN CT-UO PT                   |
    | WZ7M     | WZ7M       | ZN CT                    | ZN CT                         |
    | WZ7M     | WZ7M       | ZN CT                    | ZN CT                         |
    +----------+------------+--------------------------+-------------------------------+
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    subquery: CASE WHEN in JOIN syntax

    Hello there.
    First of all I must say that I am a newbie when it comes to MySQL.
    Here is my problem.
    I need JOIN the `tbl_flight` with `tbl_flight_name` for this output:
    Code:
    mysql> SELECT
    	TRIM(
    		SUBSTRING_INDEX(output, '-', 1)
    	)`myFlightZone`,
    	TRIM(
    		SUBSTRING_INDEX(output, '-', - 1)
    	)`myFlightArea`,
    	output,
    	 myFlight
    FROM
    	(
    		SELECT
    			CASE
    		WHEN a. myFlightZone = 'XY5Q' THEN
    			'ZwNq PtS9'
    		END  myFlight,
    
    	IF(
    		char_length(a.siglaunita)<= 4,
    		a.myFlightAreaName,
    		trim(
    			concat(
    				a.myFlight,
    				'-',
    				a.myFlightAreaName
    			)
    		)
    	)`output`
    	FROM
    		tbl_flight a
    	JOIN tbl_flight_name b ON a.myFlightAreaName = b.myFlightAreaName
    	)q;
    
    +---------------+---------------+----------------+-----------+
    | myFlightZone  | myFlightArea  | output         |  myFlight |
    +---------------+---------------+----------------+-----------+
    | XY5Q          | UO TP123      | XY5Q-UO TP123  | ZwNq PtS9 |
    | XY5Q          | UO TP123      | XY5Q-UO TP123  | ZwNq PtS9 |
    | XY5Q          | UO TP123      | XY5Q-UO TP123  | ZwNq PtS9 |
    | XY5Q          | UO TP123      | XY5Q-UO TP123  | ZwNq PtS9 |
    +---------------+---------------+----------------+-----------+
    4 rows in set
    But the CASE WHEN condition not working because I need this other output:
    Code:
    +------------------+--------------------+--------------------+-----------+
    | myFlightZone     | myFlightArea       | output             |  myFlight |
    +------------------+--------------------+--------------------+-----------+
    | XY5Q             | UO TP123           | ZwNq PtS9-UO TP123 | ZwNq PtS9 |
    | XY5Q             | UO TP123           | ZwNq PtS9-UO TP123 | ZwNq PtS9 |
    | XY5Q             | UO TP123           | ZwNq PtS9-UO TP123 | ZwNq PtS9 |
    | XY5Q             | UO TP123           | ZwNq PtS9-UO TP123 | ZwNq PtS9 |
    +------------------+--------------------+--------------------+-----------+
    I need
    PHP Code:
    CONCAT(myFlight,'-',myFlightArea
    Your help would be very appreciated.
    thanks for your time and hints.
    Cheers
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •