Help combining a mysql query

[Champak]

How can I combine

   query A 

"SELECT A.sku, A.jan, A.isbn, B.cost_amount FROM roottesCart_product AS A LEFT JOIN roottesCart_product_cost AS B ON A.product_id = B.product_id"

and

    query B

"SELECT product_id FROM roottesCart_product_attribute WHERE text='Neutral'"

 

 

Thanks

 

[Danp2]

Have you tried something like this?

SELECT A.sku, A.jan, A.isbn, B.cost_amount FROM roottesCart_product AS A LEFT JOIN roottesCart_product_cost AS B ON A.product_id = B.product_id
WHERE a.product_id IN (SELECT product_id FROM roottesCart_product_attribute WHERE text='Neutral')

 

[Champak]

Thanks

 

One correction: Query B should be

"SELECT text FROM roottesCart_product_attribute WHERE text='Neutral'"

 

Thanks, I did, but it didn't give me the results I expected. Instead of ADDING a "text" column at the end of the Query A where 'Neutral' would be on all the corresponding rows (basically Left Joining), it is reducing the entire Query A array to rows that contain the word "Neutral", the same as the one you provided.

I should add that roottesCart_product_attribute contains a column 'product_id' as well to match up with Query A...but I still can't figure how to combine them properly.

EDIT:

If it matters, roottesCart_product_attribute is "vertical structured" table (I dont know the proper terminology) see attached image.

Edited May 13, 2023 by Champak

[Danp2]

47 minutes ago, Champak said:

One correction: Query B should be

"SELECT text FROM roottesCart_product_attribute WHERE text='Neutral'"

No, that's wrong. Did you try it as I originally posted? If so, what were the results?

[Champak]

You're right, I was wrong lol...spent too long staring at this thing. Yes, I used your original example. The result is attached.

Right now what I would expect to see is a 400 row array with a col 4. (the 4th col would be the "text" col from the database) with values of  "Neutral" on some of the rows. What it's doing now is reducing the array to rows that have "Neutral" and not showing the column.

 

This is what I tried before which gets me the same result as what you did, but it shows me the 4th column which is I want to see.

"SELECT A.sku, A.jan, A.isbn, B.cost_amount, C.text FROM roottesCart_product AS A LEFT JOIN roottesCart_product_cost AS B ON A.product_id = B.product_id LEFT JOIN roottesCart_product_attribute AS C ON B.product_id = C.product_id WHERE text='Neutral'"

I just need to see the entire X amount row array with that 4th column only showing "Neutral" value.

 

 

[mistersquirrle]

I'm not that familiar with MySQL, but @Danp2s answer looks correct, just add the table and then column to their query:

SELECT
  A.sku,
  A.jan,
  A.isbn,
  B.cost_amount,
  C.attribute_id,
  C.text
FROM
  roottesCart_product AS A
  LEFT JOIN roottesCart_product_cost AS B ON A.product_id = B.product_id
  LEFT JOIN roottesCart_product_attribute AS C ON A.product_id = C.product_id
WHERE
  a.product_id IN (
    SELECT
      product_id
    FROM
      roottesCart_product_attribute
    WHERE
      attribute_id = 35
      AND text = 'Neutral'
  )
ORDER BY A.sku, C.attribute_id

How's that look?

Edited May 13, 2023 by mistersquirrle

[Danp2]

44 minutes ago, Champak said:

I just need to see the entire X amount row array with that 4th column only showing "Neutral" value.

If the 4th column is always "neutral", then why do you need it in the resulting table?

@mistersquirrle If you are going to join the 3rd table into the select, then you shouldn't need the subquery. Simply do it like this --

SELECT
  A.sku,
  A.jan,
  A.isbn,
  B.cost_amount,
  C.text
FROM
  roottesCart_product AS A
  LEFT JOIN roottesCart_product_cost AS B ON A.product_id = B.product_id
  LEFT JOIN roottesCart_product_attribute AS C ON A.product_id = C.product_id
WHERE
  c.text = 'Neutral'
ORDER BY A.sku, C.attribute_id

P.S. I dropped the use of attribute_id as this wasn't listed in the requirements AFAICS.

 

 

 

[TheXman]

I'm not sure I understand what you want in your result set.  From what I can gather, you want your result set to contain:

1. A row for each record in the product table containing the sku, jan, and isbn
2. If that product has an associated cost, then it should be in the cost column.
3. If that product has a "Neutral" attribute, then add "Neutral" to the text column, otherwise display no attribute in the text column.

If that description is correct, then the following query should produce that result:

SELECT 
    prod.sku,
    prod.jan,
    prod.isbn,
    cost.cost_amount, 
    attr.text
FROM roottesCart_product AS prod
LEFT JOIN roottesCart_product_cost AS cost 
    ON cost.product_id = prod.product_id
LEFT JOIN roottesCart_product_attribute AS attr 
    ON attr.product_id = prod.product_id
    AND attr.text = 'Neutral'

 

Edited May 13, 2023 by TheXman

[Champak]

Thanks all. Here are the results.

@mistersquirrle unfortunately that isn't getting me the expected results.

@Danp2 that is giving me the same results I'm able to get with the code I posted in the previous post with the image of the 4th column.

@TheXman that seems to be giving me exactly the results I'm after....EXCEPT, it isn't working lol. I actually tried something similar and the script froze...like your example is doing. I thought the code didn't work and abandoned it. This time I decided to input your example in phpmyadmin and it gave me the output I wanted. It just isn't working in autoit. I guess it is something to do with the "AND" part. I'm using ADO.au3 UDF. Any thoughts?

[TheXman]

 

22 minutes ago, Champak said:

Any thoughts?

The first thought that pops into my head is a question.  How am I supposed to have any thoughts about something I haven't seen, namely your script and how you are executing the query?   The second thought is why are you using the ADO UDF instead of the  MySQL Command-Line Client ?

Edited May 13, 2023 by TheXman

[Champak]

You're right, here is the script. The first query displays the array, then the second query freezes. If I was to venture a guess, it may be because the second one is populating the empty values with NULL instead of leaving them blank. That's the only difference I see between the two if I do the query in phpmyadmin. Is there a way to make sure the empty value is blank and not NULL?

expandcollapse popup

;~ #AutoIt3Wrapper_UseX64=Y

#include "ADO.au3"

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>



_Example_MySQL()
If @error Then ConsoleWrite('! ---> @error=' & @error & '  @extended=' & @extended & ' : _Example_MSSQL_SQLServerAuthorization()' & @CRLF)


Func _Example_MySQL()
    ; Link to Windows MySQL ODBC drivers
    ; https://dev.mysql.com/downloads/connector/odbc/

    _ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)
    _ADO_EVENT_Wrapper(_ADO_EVENT_UserHandler)

    Local $sDriver = 'MySQL ODBC 8.0 ANSI Driver' ; 'MySQL ODBC 5.3 UNICODE Driver'
    Local $sServer = '' ; change this string to YourServerLocation
    Local $sDatabase = '' ; change this string to YourDatabaseName
    Local $sPort = '' ; change this string to If your Server use non standard PORT
    Local $sUser = '' ; change this string to YourUserName
    Local $sPassword = '' ; change this string to YourPassword
    Local $sConnectionString = _ADO_ConnectionString_MySQL($sUser, $sPassword, $sDatabase, $sDriver, $sServer, $sPort)

    _ADO_EVENTS_ShowOnly_InfoMessages(True)

    $uuu = _Example_2_RecordsetDisplay($sConnectionString, "SELECT A.sku, A.jan, A.isbn, B.cost_amount FROM roottesCart_product AS A LEFT JOIN roottesCart_product_cost AS B ON A.product_id = B.product_id")
    _ArrayDisplay($uuu)
    
    $uuu = _Example_2_RecordsetDisplay($sConnectionString, "SELECT prod.sku, prod.jan, prod.isbn, cost.cost_amount, attr.text FROM roottesCart_product AS prod LEFT JOIN roottesCart_product_cost AS cost ON cost.product_id = prod.product_id LEFT JOIN roottesCart_product_attribute AS attr ON attr.product_id = prod.product_id AND attr.text = 'Neutral'")
    _ArrayDisplay($uuu)

EndFunc   ;==>_Example_MySQL

#Region Common / internal

Func _Example_2_RecordsetDisplay($sConnectionString, $sQUERY)
Local $11, $22, $33
    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query directly to Array of Arrays (instead to $oRecordset)
    Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)


    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null


    $33 = _ADO_RecordsetArray_GetContent($aRecordset)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    If IsArray($33) Then MsgBox(0,0,"Array detected")

    Return $33




    ; Display Array Content with column names as headers

;   

EndFunc   ;==>_Example_2_RecordsetDisplay

#EndRegion Common / internal

Func _COMErrorDescription_UserStore($sDescription = Default)
    Local Static $sDescription_static = ''
    If $sDescription <> Default Then $sDescription_static = $sDescription
    Return $sDescription_static
EndFunc   ;==>_COMErrorDescription_UserStore

Func _User_COMErrorHandler_Function($oError)
    ConsoleWrite( _
            @ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & _
            "$oError.description is: " & @TAB & $oError.description & @CRLF & _
            "$oError.windescription: " & @TAB & $oError.windescription & @CRLF & _
            "$oError.number is: " & @TAB & Hex($oError.number, 8) & @CRLF & _
            "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "$oError.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "$oError.source is : " & @TAB & $oError.source & @CRLF & _
            "$oError.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF _
            )
    _COMErrorDescription_UserStore($oError.description) ; store description to use it outsided UDF in your own function
EndFunc   ;==>_User_COMErrorHandler_Function

Func _ADO_EVENT_UserHandler($param0, ByRef $oConnection_param1, ByRef $oCommand_param3, ByRef $oRecordset_param3, ByRef $oError_param4, $param5 = Null, $param6 = Null, $param7 = Null, $param8 = Null, $param9 = Null)
    Switch $param0
        Case 'Disconnect'
            MsgBox($MB_OK + $MB_TOPMOST + $MB_ICONINFORMATION, 'Information', 'ADO Connection was disconnected.')
        Case Else
    EndSwitch
    #forceref $param0, $oConnection_param1, $oCommand_param3, $oRecordset_param3, $oError_param4, $param5, $param6, $param7, $param8, $param9
EndFunc   ;==>_ADO_EVENT_UserHandler

 

And as far as doing it by ways of CMD, I simply didn't know about doing it that way nor do I have very much experience with it. I did a search for mysql and found a bunch of UDFs and no mention of command...that I saw before. And this UDF seemed to be the most popular. I was looking for something quick and simple to resolve an issue with an already made app that I didn't have to do too much coding with and dust the cobwebs off my brain with the coding lol.

So now that you know.......thoughs lol?

 

EDIT:

Figured it out, thanks all. Had to add IFNULL for the text column

SELECT prod.sku, prod.jan, prod.isbn, cost.cost_amount, IFNULL(attr.text,'') FROM roottesCart_product AS prod LEFT JOIN roottesCart_product_cost AS cost ON cost.product_id = prod.product_id LEFT JOIN roottesCart_product_attribute AS attr ON attr.product_id = prod.product_id AND attr.text = 'Neutral'"

 

Edited May 13, 2023 by Champak